﻿using Model;
using System;
using System.Data;

namespace BLL
{
    public class YX_TransferChequeBLL : BaseBLL
    {
        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT r,
       N_TFCHEQUE_ID,
       CMY_NAME,
       C_NAME,
       C_TFCHEQUE_DRAWERACCOUNT,
       C_TFCHEQUE_PAYEECMP,
       C_TFCHEQUE_PAYEEBANK,
       C_TFCHEQUE_PAYEEACCOUNT,
       C_TFCHEQUE_PAYEENAME,
       N_TFCHEQUE_INVOICEVALUE,
       to_char(D_TFCHEQUE_STARTDATE, 'yyyy-mm-dd') D_TFCHEQUE_STARTDATE,
       C_TFCHEQUE_CODE,
       C_TFCHEQUE_REMARK
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
               YXTC.N_TFCHEQUE_ID AS N_TFCHEQUE_ID,
               SCOM.CMY_NAME AS CMY_NAME,
               SBAN.C_NAME AS C_NAME,
               YXTC.C_TFCHEQUE_DRAWERACCOUNT AS C_TFCHEQUE_DRAWERACCOUNT,
               YXTC.C_TFCHEQUE_PAYEECMP AS C_TFCHEQUE_PAYEECMP,
               YXTC.C_TFCHEQUE_PAYEEBANK AS C_TFCHEQUE_PAYEEBANK,
               YXTC.C_TFCHEQUE_PAYEEACCOUNT AS C_TFCHEQUE_PAYEEACCOUNT,
               YXTC.C_TFCHEQUE_PAYEENAME AS C_TFCHEQUE_PAYEENAME,
               YXTC.N_TFCHEQUE_INVOICEVALUE AS N_TFCHEQUE_INVOICEVALUE,
               YXTC.D_TFCHEQUE_STARTDATE AS D_TFCHEQUE_STARTDATE,
               YXTC.C_TFCHEQUE_CODE AS C_TFCHEQUE_CODE,
               YXTC.C_TFCHEQUE_REMARK AS C_TFCHEQUE_REMARK
          FROM YX_TransferCheque YXTC
          LEFT JOIN YX_ACCOUNT YXAT
            ON YXTC.C_TFCHEQUE_DRAWERACCOUNT = YXAT.ACCOUNTNO
          LEFT JOIN SYS_COMPANY SCOM
            ON YXTC.N_TFCHEQUE_DRAWERCMPID = SCOM.CMY_ID
          LEFT JOIN SYS_BANK SBAN
            ON YXTC.N_TFCHEQUE_DRAWERBANKID = SBAN.N_ID
         WHERE /*YXAT.COMPANY_ID=SCOM.CMY_ID
         AND YXAT.N_BANK_ID=SBAN.N_ID
         AND*/ YXAT.ACCOUNTTYPE = 1 ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListByPage", "查询队列", "");
        }

        public int GetRecordCount(string whereStr)
        {
            //           string sql = string.Format(@"SELECT COUNT(1)
            // FROM YX_TransferCheque YXTC
            // LEFT JOIN YX_ACCOUNT YXAT
            //   ON YXTC.C_TFCHEQUE_DRAWERACCOUNT = YXAT.ACCOUNTNO
            // LEFT JOIN SYS_COMPANY SCOM
            //   ON YXTC.N_TFCHEQUE_DRAWERCMPID = SCOM.CMY_ID
            // LEFT JOIN SYS_BANK SBAN
            //   ON YXTC.N_TFCHEQUE_DRAWERBANKID = SBAN.N_ID
            //WHERE /*YXAT.COMPANY_ID=SCOM.CMY_ID
            //AND */YXAT.N_BANK_ID=SBAN.N_ID
            //AND YXAT.ACCOUNTTYPE = 1 " + whereStr);

            string sql = string.Format(@"SELECT COUNT(*)
  FROM (SELECT
               YXTC.N_TFCHEQUE_ID AS N_TFCHEQUE_ID,
               SCOM.CMY_NAME AS CMY_NAME,
               SBAN.C_NAME AS C_NAME,
               YXTC.C_TFCHEQUE_DRAWERACCOUNT AS C_TFCHEQUE_DRAWERACCOUNT,
               YXTC.C_TFCHEQUE_PAYEECMP AS C_TFCHEQUE_PAYEECMP,
               YXTC.C_TFCHEQUE_PAYEEBANK AS C_TFCHEQUE_PAYEEBANK,
               YXTC.C_TFCHEQUE_PAYEEACCOUNT AS C_TFCHEQUE_PAYEEACCOUNT,
               YXTC.C_TFCHEQUE_PAYEENAME AS C_TFCHEQUE_PAYEENAME,
               YXTC.N_TFCHEQUE_INVOICEVALUE AS N_TFCHEQUE_INVOICEVALUE,
               YXTC.D_TFCHEQUE_STARTDATE AS D_TFCHEQUE_STARTDATE,
               YXTC.C_TFCHEQUE_CODE AS C_TFCHEQUE_CODE,
               YXTC.C_TFCHEQUE_REMARK AS C_TFCHEQUE_REMARK
          FROM YX_TransferCheque YXTC
          LEFT JOIN YX_ACCOUNT YXAT
            ON YXTC.C_TFCHEQUE_DRAWERACCOUNT = YXAT.ACCOUNTNO
          LEFT JOIN SYS_COMPANY SCOM
            ON YXTC.N_TFCHEQUE_DRAWERCMPID = SCOM.CMY_ID
          LEFT JOIN SYS_BANK SBAN
            ON YXTC.N_TFCHEQUE_DRAWERBANKID = SBAN.N_ID
         WHERE /*YXAT.COMPANY_ID=SCOM.CMY_ID
         AND YXAT.N_BANK_ID=SBAN.N_ID
         AND*/ YXAT.ACCOUNTTYPE = 1 ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";

            int count = GetCount(sql);

            return count;
        }

        /// <summary>
        /// 根据编号查询银行汇票明细
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public YX_TransferCheque SelectById(string id)
        {
            string sql = string.Format(@"select * from YX_TransferCheque a where a.N_TFCHEQUE_ID= " + id);

            DataTable dt = Query(sql);

            YX_TransferCheque model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    model = new YX_TransferCheque();
                    model.N_TFCHEQUE_ID = Convert.ToDecimal(id);
                    model.N_TFCHEQUE_DRAWERCMPID = decimal.Parse(dr["N_TFCHEQUE_DRAWERCMPID"].ToString());
                    model.N_TFCHEQUE_DRAWERBANKID = decimal.Parse(dr["N_TFCHEQUE_DRAWERBANKID"].ToString());
                    model.C_TFCHEQUE_DRAWERACCOUNT = dr["C_TFCHEQUE_DRAWERACCOUNT"].ToString();
                    model.C_TFCHEQUE_PAYEECMP = dr["C_TFCHEQUE_PAYEECMP"].ToString();
                    model.C_TFCHEQUE_PAYEEBANK = dr["C_TFCHEQUE_PAYEEBANK"].ToString();
                    model.C_TFCHEQUE_PAYEEACCOUNT = dr["C_TFCHEQUE_PAYEEACCOUNT"].ToString();
                    model.D_TFCHEQUE_STARTDATE = Convert.ToDateTime(dr["D_TFCHEQUE_STARTDATE"].ToString());
                    model.C_TFCHEQUE_PAYEENAME = dr["C_TFCHEQUE_PAYEENAME"].ToString().ToString();
                    model.N_TFCHEQUE_INVOICEVALUE = Convert.ToDecimal(dr["N_TFCHEQUE_INVOICEVALUE"].ToString());
                    model.C_TFCHEQUE_CODE = dr["C_TFCHEQUE_CODE"].ToString();
                    model.C_TFCHEQUE_REMARK = dr["C_TFCHEQUE_REMARK"].ToString();
                }
            }

            return model;
        }

        public int INSERT(YX_TransferCheque model, string operName)
        {
            model.N_TFCHEQUE_ID = GetSequence("SEQ_N_TFCHEQUE_ID");
            string sql = string.Format(@"insert into yx_transfercheque
  (n_tfcheque_id,
   n_tfcheque_drawercmpid,
   n_tfcheque_drawerbankid,
   c_tfcheque_draweraccount,
   c_tfcheque_payeecmp,
   c_tfcheque_payeebank,
   c_tfcheque_payeeaccount,
   n_tfcheque_invoicevalue,
   d_tfcheque_startdate,
   c_tfcheque_code,
   c_tfcheque_remark,
   c_tfcheque_payeename)
values
  ({0},
   {1},
   {2},
   '{3}',
   '{4}',
   '{5}',
   '{6}',
   {7},
   TO_DATE('{8}','YYYY-MM-DD HH24:MI:SS'),
   '{9}',
   '{10}',
   '{11}')", model.N_TFCHEQUE_ID, model.N_TFCHEQUE_DRAWERCMPID, model.N_TFCHEQUE_DRAWERBANKID, model.C_TFCHEQUE_DRAWERACCOUNT, model.C_TFCHEQUE_PAYEECMP, model.C_TFCHEQUE_PAYEEBANK, model.C_TFCHEQUE_PAYEEACCOUNT, model.N_TFCHEQUE_INVOICEVALUE, model.D_TFCHEQUE_STARTDATE, model.C_TFCHEQUE_CODE, model.C_TFCHEQUE_REMARK, model.C_TFCHEQUE_PAYEENAME);

            int count = NoQuery(sql, "新增银行汇票", "INSERT", operName);
            return count;
        }

        public int UPDATE(YX_TransferCheque model, string operName)
        {
            string sql = string.Format(@"update yx_transfercheque
   set n_tfcheque_drawercmpid   = {0},
       n_tfcheque_drawerbankid  = {1},
       c_tfcheque_draweraccount = '{2}',
       c_tfcheque_payeecmp      = '{3}',
       c_tfcheque_payeebank     = '{4}',
       c_tfcheque_payeeaccount  = '{5}',
       n_tfcheque_invoicevalue  = {6},
       d_tfcheque_startdate     = TO_DATE('{7}','YYYY-MM-DD HH24:MI:SS'),
       c_tfcheque_code          = '{8}',
       c_tfcheque_remark        = '{9}',
       c_tfcheque_payeename     = '{10}'
 where n_tfcheque_id = {11}", model.N_TFCHEQUE_DRAWERCMPID, model.N_TFCHEQUE_DRAWERBANKID, model.C_TFCHEQUE_DRAWERACCOUNT, model.C_TFCHEQUE_PAYEECMP, model.C_TFCHEQUE_PAYEEBANK, model.C_TFCHEQUE_PAYEEACCOUNT, model.N_TFCHEQUE_INVOICEVALUE, model.D_TFCHEQUE_STARTDATE, model.C_TFCHEQUE_CODE, model.C_TFCHEQUE_REMARK, model.C_TFCHEQUE_PAYEENAME, model.N_TFCHEQUE_ID);
            int count = NoQuery(sql, operName);
            return count;
        }
    }
}